import smtplib
import math
import sys
from email.header import Header
from email.mime.text import MIMEText

import psycopg2


class AutoSendEmail:
    def __init__(self, db):
        self.db = db
        self.v_rows = []
        self.table_rows = ''
        self.func_dict = {
            'FU_AC_JDBC': ["接入层测试", 3, ['developer', 'tester']],
            'FU_AC_ODBC': ["接入层测试", 3, []],
            'FU_AC_PYTHON': ["接入层测试", 3, []],
            'FU_SQ_GRAM': ["SQL层测试", 8, []],
            'FU_SQ_PROC': ["SQL层测试", 8, []],
            'FU_SQ_SYS': ["SQL层测试", 8, []],
            'FU_SQ_PERF': ["SQL层测试", 8, []],
            'FU_SQ_KEYWORD': ["SQL层测试", 8, []],
            'FU_ST_INDEX': ["存储层测试", 7, []],
            'FU_ST_PART': ["存储层测试", 7, []],
            'FU_ST_MOT': ["存储层测试", 7, []],
            'FU_ST_MATER': ["存储层测试", 7, []],
            'FU_ST_FULLINDEX': ["存储层测试", 7, []],
            'DF_MA_DROPNODE': ["管理测试", 6, []],
            'DF_MA_UPGRADE': ["管理测试", 6, []],
            'DF_MA_TOOLS': ["管理测试", 6, []],
            'DF_SE_IDENTITY': ["数据安全测试", 8, []],
            'DF_SE_VISIT': ["数据安全测试", 8, []],
            'DF_SE_THREE': ["数据安全测试", 8, []],
            'DF_SE_AUDIT': ["数据安全测试", 8, []],
            'DF_SE_MODIFY': ["数据安全测试", 8, []],
            'DF_SE_ENCRYPT': ["数据安全测试", 8, []],
            'DF_SE_MASK': ["数据安全测试", 8, []],
            'DF_SE_ATTACK': ["数据安全测试", 5, []],
            'DF_HA_BACKUP': ["高可用能力", 12, []],
            'DF_HA_DELAYBACK': ["高可用能力", 12, []],
            'DF_HA_DUMP': ["高可用能力", 12, []],
            'DF_HA_COPY': ["高可用能力", 12, []],
            'DF_HA_GUC': ["高可用能力", 12, []]}

        self.reliability_dict = {
            'DB_SYSTEM': ["数据库系统类", 1, ['developer', 'tester']],
            'HARDWARE': ["硬件故障类", 2, []],
            'HUMAN': ["人为因素类", 1, []],
            'OS': ["操作系统类", 2, []],
            'PORT': ["端口故障类", 1, []],
            'RTO': ["极致RTO", 1, []],
            'TOOLS': ["工具类", 1, []],
            'ZLONGTIME': ["LONGTIME", 1, []]}

    @staticmethod
    def cal_rate(dividends, divisor):
        orgin_rate = int(dividends) / int(divisor) * 100
        if math.floor(orgin_rate) < 100 and math.ceil(orgin_rate) == 100:
            spot_index = str(orgin_rate).index('.')
            rate = float(str(orgin_rate)[:spot_index + 3:])
        elif math.floor(orgin_rate) == 100 and math.ceil(orgin_rate) == 100:
            rate = 100
        else:
            rate = round(orgin_rate, 2)

        return rate

    def get_data(self, model_table, v_feature):
        category_list = []
        cur = self.db.cursor()
        print('执行查询语句')
        sql_cmd = f"select model_name, sum(total_num) as total_num, " \
            f"sum(pass_num) as pass_num, sum(fail_num) as fail_num, " \
            f"sum(block_num) as block_num, min(start_time) as start_time, " \
            f"sum(run_time) as run_time " \
            f"from \"{model_table}\" " \
            f"group by model_name " \
            f"order by model_name;"
        cur.execute(sql_cmd)
        rows = cur.fetchall()
        for i in range(len(rows)):
            if 'func' in model_table:
                dict_name = self.func_dict
            else:
                dict_name = self.reliability_dict

            if rows[i][0].endswith('_PY'):
                schd_name_prefix = rows[i][0][:-3:]
            elif rows[i][0].endswith('_SQL'):
                schd_name_prefix = rows[i][0][:-4:]
            elif '_PY_' in rows[i][0]:
                schd_name_prefix = rows[i][0].split('_PY_')[0]
            else:
                schd_name_prefix = rows[i][0].split('_SQL_')[0]
            category_list.append(dict_name.get(schd_name_prefix, ["", ""])[0])
            colspan = dict_name.get(schd_name_prefix, ["", ""])[1]
            develop_owner = dict_name.get(schd_name_prefix, ["", ""])[-1][0]
            test_owner = dict_name.get(schd_name_prefix, ["", ""])[-1][1]

            if i >= 1 and category_list[-1] == category_list[-2]:
                td_row = ''
            else:
                td_row = f'<td rowspan="{colspan}">{category_list[-1]}</td>'

            self.table_rows += f'''
                <tr bgcolor='#f5f5d6'>
                    {td_row}
                    <td>{rows[i][0]}</td>
                    <td>{rows[i][1]}</td>
                    <td>{self.cal_rate(rows[i][2], rows[i][1])}</td>
                    <td>{rows[i][2]}</td>
                    <td>{rows[i][3]}</td>
                    <td>{rows[i][4]}</td>
                    <td>{develop_owner}</td>
                    <td>{test_owner}</td>
                </tr> '''

        sql_cmd = f"select sum(total_num) as total_num, " \
            f"sum(pass_num) as pass_num, " \
            f"sum(fail_num) as fail_num, " \
            f"sum(block_num) as block_num " \
            f"from \"{model_table}\""
        cur.execute(sql_cmd)
        rows = cur.fetchall()

        self.table_rows += f'''<tr bgcolor='#d9ffcc'> 
                        <td colspan="2">合计</td>
                        <td>{rows[0][0]}</td> 
                        <td>{self.cal_rate(rows[0][1], rows[0][0])}</td>
                        <td>{rows[0][1]}</td> 
                        <td>{rows[0][2]}</td> 
                        <td>{rows[0][3]}</td>
                        <td></td>
                        <td></td> 
                    </tr> '''

        sql_cmd = f"set time zone 'PRC'; " \
            f"select * from \"ReportApp_dbverison\" " \
            f"where feature='{v_feature}';"
        cur.execute(sql_cmd)
        self.v_rows = cur.fetchall()


if __name__ == '__main__':
    Usage = "Usage:input as " \
            "'python3 auto_send_email.py feature tier' "
    if len(sys.argv) < 2:
        print(Usage)
        exit(0)
    # 取值范围[function, reliability]
    feature = sys.argv[1]
    tier = sys.argv[2]
    if feature == "function":
        if tier == '2':
            t_name_model = "ReportApp_tier2funcmodel"
            title_text = 'openGauss自动化Tier2-CI连跑结果'
            report_url = 'http://10.190.105.219:8000/tier2/'
            db_version_feature = 'tier2_function'
        else:
            t_name_model = "ReportApp_tier3funcmodel"
            title_text = 'openGauss自动化Tier3-CI连跑结果'
            report_url = 'http://10.190.105.219:8000/tier3/'
            db_version_feature = 'tier3_function'
    else:
        t_name_model = "ReportApp_reliamodel"
        title_text = 'openGauss自动化Tier3-可靠性CI连跑结果'
        report_url = 'http://xx.xx.xxx.xxx:8000/reliability/'
        db_version_feature = 'tier3_reliability'

    mail_host = "smtpextest.huawei.com"
    mail_user = "openGaussTest"
    mail_pass = "sua@99@wei"

    sender = ''
    to_receivers = []

    cc_receivers = []

    print('连接数据库')
    my_db = psycopg2.connect(database='autotest_report',
                             user='u_report',
                             password='sua@222*w',
                             host='xx.xxx.xxx.xxx',
                             port=50517)
    AutoSendEmail = AutoSendEmail(my_db)
    AutoSendEmail.get_data(t_name_model, db_version_feature)
    print('断开数据库连接')
    mail_msg = f'''<!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    
    <title>{title_text}</title>
    
    <style>
        table {{
            width: 100%;
            border-spacing: 0;
            border-collapse: collapse;
            table-layout: fixed;
            word-break: break-all;
        }}
    
        table th {{
            text-align: center;
        }}
    
        table td {{
            padding:10px 30px;
            text-align: center;
        }}
    </style>
    </head>
    
    <body>
        <p>本邮件由系统自动发出，无需回复！</p>	
        <p>各位同事，大家好，以下为openGauss自动化连跑信息:</p>
        <table id="t_SumModel" border="1" onload="func_load()"> 
            <tr> 
                <th colspan="9"><h1>{title_text}</h1></th> 
            </tr> 
            <tr bgcolor='#d9ffcc'> 
                <td colspan="2">数据库版本: </td>
                <td colspan="7">{AutoSendEmail.v_rows[0][-2]}</td>
            </tr>
            <tr bgcolor='#d9ffcc'> 
                <td colspan="2">安装时间: </td>
                <td colspan="7" stytle="text-align: left;">{
    AutoSendEmail.v_rows[0][-1]}</td>
            </tr>
            <tr bgcolor='#d9ffcc'> 
                <td stytle="width: '8%'">规格</td> 
                <td stytle="width: '20%'">测试套名</td> 
                <td stytle="width: '8%'">用例总数</td> 
                <td stytle="width: '8%'">成功率（%）</td>
                <td stytle="width: '6%'">成功</td>
                <td stytle="width: '6%'">失败</td>
                <td stytle="width: '6%'">阻塞</td>
                <td stytle="width: '19%'">开发责任人</td>
                <td stytle="width: '19%'">测试责任人</td>
            </tr> 
            {AutoSendEmail.table_rows}
        </table>
        <p>详细报告：<a href="{report_url}"> 点击跳转>> </a></p>
    </body>
    </html>
    '''

    receivers = to_receivers + cc_receivers
    message = MIMEText(mail_msg, 'html', 'utf-8')
    message['From'] = Header(sender, 'utf-8')  # 发送者
    message['To'] = Header(",".join(to_receivers))  # 接收者
    message['Cc'] = Header(",".join(cc_receivers))
    subject = title_text
    message['Subject'] = Header(subject, 'utf-8')
    try:
        smtp_obj = smtplib.SMTP()
        smtp_obj.connect(mail_host, 25)
        smtp_obj.login(mail_user, mail_pass)
        smtp_obj.sendmail(sender, receivers, message.as_string())
        print("邮件发送成功")
        smtp_obj.quit()
    except smtplib.SMTPException as e:
        print("Error: 无法发送邮件\n", e)
    finally:
        my_db.close()
